﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class SanBay_DAO
    {
        #region ham truy van du lieu

        public static List<SanBay_DTO> layDanhSachSanBay()
        {
            string query = "select * from SANBAY";

            List<SanBay_DTO> list = new List<SanBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                SanBay_DTO sb = new SanBay_DTO();
                sb.maSB = dr["MaSB"].ToString();
                sb.quocGia = dr["QuocGia"].ToString();
                sb.tenSB = dr["TenSB"].ToString();
                sb.tinh = dr["Tinh"].ToString();
                list.Add(sb);
            }
            return list;
        }
        public static SanBay_DTO laySanBayTheoMaSanBay(String maSB)
        {
            string query = "select * FROM sanbay where MaSB = '" + maSB + "'";
            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            SanBay_DTO sb = new SanBay_DTO();
            foreach (DataRow dr in dt.Rows)
            {
                sb.maSB = dr["MaSB"].ToString();
                sb.quocGia = dr["QuocGia"].ToString();
                sb.tenSB = dr["TenSB"].ToString();
                sb.tinh = dr["Tinh"].ToString();
            }

            return sb;
        }

        public static SanBay_DTO laySanBayDenTheoMaCB(string MaCB)
        {
            string query = "select sb.* from chitietsb ct join sanbay sb on ct.MaSB = sb.MaSB " +
                "where ct.Loai = 2 and ct.MaCB = '" + MaCB + "'";
            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            SanBay_DTO sb = new SanBay_DTO();
            foreach (DataRow dr in dt.Rows)
            {
                sb.maSB = dr["MaSB"].ToString();
                sb.quocGia = dr["QuocGia"].ToString();
                sb.tenSB = dr["TenSB"].ToString();
                sb.tinh = dr["Tinh"].ToString();
            }
            return sb;
        }

        public static SanBay_DTO laySanBayDiTheoMaCB(string MaCB)
        {
            string query = "select sb.* from chitietsb ct join sanbay sb on ct.MaSB = sb.MaSB " +
                "where ct.Loai = 1 and ct.MaCB = '" + MaCB + "'";
            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            SanBay_DTO sb = new SanBay_DTO();
            foreach (DataRow dr in dt.Rows)
            {
                sb.maSB = dr["MaSB"].ToString();
                sb.quocGia = dr["QuocGia"].ToString();
                sb.tenSB = dr["TenSB"].ToString();
                sb.tinh = dr["Tinh"].ToString();
            }
            return sb;
        }

        public static List<SanBay_DTO> laySBTGTheoMaCB(string macb)
        {
            string query = "select sb.* from chitietsb ct join sanbay sb on ct.MaSB = sb.MaSB " +
          "where ct.Loai = 3 and ct.MaCB = '" + macb + "'";
            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            List<SanBay_DTO> a = new List<SanBay_DTO>();

            foreach (DataRow dr in dt.Rows)
            {
                SanBay_DTO sb = new SanBay_DTO();

                sb.maSB = dr["MaSB"].ToString();
                sb.quocGia = dr["QuocGia"].ToString();
                sb.tenSB = dr["TenSB"].ToString();
                sb.tinh = dr["Tinh"].ToString();

                a.Add(sb);
            }
            return a;
        }


        #endregion

        #region Cac ham tuong tac voi co so du lieu


        //        MaSB VARCHAR(3) NOT NULL,
        //TenSB nvarchar(30),
        //QuocGia nvarchar(30),
        //Tinh nvarchar(30),

        public static bool themSanBay(SanBay_DTO sanbay)  // them san bay
        {

            string query = "insert into SANBAY (MaSB, QuocGia, Tinh, TenSB)"
                + " values ('" + sanbay.maSB + "', '" + sanbay.quocGia + "', '" + sanbay.tinh + "', '" + sanbay.tenSB + "')";

            int n = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (n == 1)
                return true;

            return false;

        }

        //kiem tra khoa cua san bay them  vao co ton tai trong du lieu hay chua
        public static bool kiemTraSanBayDaTonTai(String MaSB)
        {
            string query = "select * from sanbay where MaSB = '" + MaSB + "'";

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            if (dt.Rows.Count == 1)
            {
                return true;
            }
            return false;
        }

        public static bool suaSanBay(SanBay_DTO sanbay)   // sua san bay
        {

            string query = "update sanbay set TenSB = '" + sanbay.tenSB + "', QuocGia = '" + sanbay.quocGia + "', Tinh = '" + sanbay.tinh + "'" +
                "where MaSB = '" + sanbay.maSB + "'";


            int n = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (n == 1)
                return true;
            return false;

        }
        public static bool xoaSanBay(string MaSB)   // xoa san bay
        {

            List<CT_SanBay_DTO> list = CT_SanBay_DAO.layDanhSachCTSanBay();

            for (int i = 0; i < list.Count; i++)
                if (list[i].maSB == MaSB)
                    ChuyenBay_DAO.xoaChuyenBay(list[i].maCB);

            string query = "delete sanbay where MaSB = '" + MaSB + "' ";

            int n = SqlDataAccessHelper.ExecuteNoneQuery(query);
            if (n == -1)
                return false;

            return true;

        }
        #endregion

    }
}
